The ADO Command object defines a command or a query that you can execute on a data source. Command objects are useful when you plan to execute the same command or query several times (on the same or on a different data source) and when you want to run stored procedures or parameterized queries. Recall from earlier in this chapter that you can execute SQL queries and commands using the Execute method of a Connection object or the Open method of a Recordset object. In a real application, you're more likely to use Command objects for these tasks. When you're working with SQL Server, for example, Command objects automatically reuse the temporary stored procedure that SQL Server creates the first time you execute it, and you can pass different arguments each time you execute the query.
You can create stand-alone Command objects that aren't associated with a Connection object, and you can then establish the connection by assigning a valid connection object to the ActiveConnection property. By doing so, you can reuse the same command on multiple connections.
The Command object supports nine properties, but only two of them are really necessary for carrying out a query or a command.
The most important property of the Command object is CommandText, which sets or returns the SQL command or query, the name of a table, or the name of a stored procedure. If you use an SQL query, it should be in the dialect of the database engine you're connecting to. Depending on the string that you assign to this property and on the particular provider you're using, ADO might change the contents of this property. For this reason, after assigning a value to CommandText, you might be wise to read it back to check the actual value that will be used for the query. Here's an example of how you use this property:
Dim cmd As New ADODB.Command cmd.CommandText = "SELECT * FROM Employees WHERE BirthDate > #1/1/1960" |
If you're going to repeat the query or the command with different arguments, it's convenient to prepare a parameterized Command object, which you do by inserting ? (question mark) symbols in the CommandText property:
Dim cmd As New ADODB.Command
cmd.CommandText = "SELECT * FROM Employees WHERE BirthDate > ? " _
& "AND HireDate > ?"
|
The CommandText property tells the Command object what to do, and the ActiveConnection property specifies on which data source the command should be carried out. This property can be assigned a connection string (that follows the syntax of the ConnectionString of the Connection object) or a Connection object that already points to a data source. When you set this property to Nothing, you disconnect the Command object and release all the resources allocated on the server. If you attempt to run the Execute method before assigning a connection string or a Connection object to this property, a run-time error occurs. An error also occurs if you assign a closed Connection object to this property. Some providers require you to set this property to Nothing before switching to another connection.
You can share the connection among multiple ADO Command objects only if you assign the same Connection object to their ActiveConnection properties. Simply assigning the same connection string creates distinct connections. Here's an example that leverages this capability:
' Edit this constant to match your directory structure.
Const DBPATH = "C:\Program Files\Microsoft Visual Studio\Vb98\NWind.mdb"
' Create the first Command object.
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset
cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source= " & DBPATH
cmd.CommandText = "SELECT FirstName, LastName FROM Employees"
Set rs = cmd.Execute()
' Create a second Command object on the same database connection.
Dim cmd2 As New ADODB.Command, rs2 As New ADODB.Recordset
Set cmd2.ActiveConnection = cmd.ActiveConnection
cmd2.CommandText = "SELECT * FROM Customers"
Set rs2 = cmd2.Execute()
|
Setting the ActiveConnection property to Nothing can affect the Parameters collection. More precisely, if the provider has populated the Parameters collection automatically, the collection will be cleared when the ActiveConnection is set to Nothing. If the Parameters collection has been manually populated through code, setting the ActiveConnection to Nothing doesn't affect it.
The CommandType property lets you optimize execution speed by indicating what the CommandText string contains. It can be one of the following enumerated constants:
| Value | Description |
|---|---|
| 1-adCmdText | The text of an SQL query. |
| 2-adCmdTable | A database table. |
| 4-adCmdStoredProc | A stored procedure. |
| 8-adCmdUnknown | The provider will determine the correct type (the default). |
| 512-adCmdTableDirect | A database table opened directly. |
If you don't specify a value for this property, or if you use adCmdUnknown, you force ADO to figure out by itself what the CommandText string is, an operation that usually adds considerable overhead. The adCmdStoredProc option can also improve performance because it prevents ADO from creating a temporary stored procedure before executing the query. If the value you assign to the CommandType property doesn't match the type of the CommandText string, a run-time error occurs.
The Prepared property lets you leverage the real power of the Command object. When this property is True, the provider creates a compiled (prepared) version of the query passed in the CommandText property and then uses it to run the query each time this command is reexecuted. Creating a compiled procedure takes some time, so you should set this property to True only if you plan to execute the query two or more times. If the data source doesn't support prepared statements, what happens depends on the provider: It can raise an error, or it can just ignore the assignment.
The CommandTimeout property sets or returns the number of seconds ADO will wait when a command executes before raising an error. The default value is 30 seconds. If you set this property to 0, ADO will wait forever. This value isn't inherited from the CommandTimeout property of the Connection object to which the Command object is connected. In addition, not all providers support this property.
State is a read-only property that can be queried to understand what the Command is currently doing. It can return the value 0-adStateClosed (the Command object is inactive) or 4-adStateExecuting (the Command object is executing a command).
The most important method of the Command object is the Execute method, which runs the query or the command stored in the CommandText property. This method is similar to the Connection object's Execute method, but the syntax is slightly different because the text of the query can't be passed as an argument:
Execute([RecordsAffected], [Parameters], [Options]) As Recordset |
If the CommandText property contains a row-returning query, the Execute method returns an open Recordset (which doesn't necessarily contain any row, however). Conversely, if the CommandText property specifies an action query, this method returns a closed Recordset. In the latter case, you can effectively use Execute as a procedure instead of as a function and ignore its return value.
If you pass a Long variable as the RecordsAffected argument, the Execute method returns in the variable the number of records that were affected by the action query command. This argument is optional, so you aren't obliged to pass anything, and it doesn't return any meaningful value with row-returning queries. If you're running a parameterized command or query, Parameters is a Variant that contains the value of a parameter or an array of all the expected parameters:
' Edit this constant to match your directory structure.
Const DBPATH = "C:\Program Files\Microsoft Visual Studio\Vb98\NWind.mdb"
Dim cmd As New ADODB.Command, rs As New ADODB.Recordset
cmd.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source= " & DBPATH
cmd.CommandText = "SELECT * FROM Employees WHERE BirthDate > ? " _
& "AND HireDate > ?"
cmd.CommandType = adCmdText
' You can pass multiple parameters without using a temporary array.
Set rs = cmd.Execute(, Array(#1/1/1960#, #1/1/1994#))
|
The parameters you pass in this way are valid only for the current execution of the command, and they don't affect the Parameters collection. If you omit one or more arguments in the Execute method, ADO uses the corresponding values in the Parameters collection. Here's an example that passes only the second argument and implicitly uses the first item in the Parameters collection:
Set rs = cmd.Execute(, Array(, #1/1/1994#)) |
CAUTION
Although you can pass any number of parameters using this method, you can't retrieve output parameters in this way. If you want to execute a stored procedure with output parameters, you can retrieve their values only by using the Parameters collection.
The Option argument can contain the same value that you assign to the CommandType property plus one or more of the following constants:
| Value | Description |
|---|---|
| 16-adAsyncExecute | Runs the command asynchronously in a separate thread |
| 32-adAsyncFetch | Fetches the results asynchronously for Recordsets based on client-side cursors |
| 64-adAsyncFetchNonBlocking | Similar to adAsyncFetch, but the calling program is never blocked |
| 128-adExecuteNoRecords | Specifies that a command of type adCmdText or adCmdStoredProc is an action query and doesn't return a Recordset |
There are subtle differences among the adAsyncExecute, adAsyncFetch, and adAsyncFetchNonBlocking options. All of them execute the command asynchronously and set AffectedRecords to -1 because when the method returns, the command hasn't completed yet and ADO doesn't know how many records will be affected. If you specify adAsyncExecute, the command is executed asynchronously, and when the provider completes it, ADO fires an ExecutionComplete event in your program. If you specify adAsyncFetch on a Recordset based on a client-side cursor, when the execution of the command completes, ADO starts fetching the result rows asynchronously: When the code asks for a row that hasn't been retrieved yet, the application is blocked until the data is available (and when this occurs, a FetchComplete event fires). The adAsyncFetchNonBlocking option is similar to adAsyncFetch but with an important difference: When the code asks for a row that hasn't been retrieved yet, the application isn't blocked and the Recordset's EOF property is set to True. The code can therefore try again later or wait for the FetchComplete event to determine when data is finally available.
You can cancel the execution of an asynchronous operation by using the Command object's Cancel method. This method raises an error if no asynchronous option was specified for the most recent Execute method.
The third method the Command object supports is CreateParameter. With this method, you can create the Parameters collection entirely in code, without a round-trip to the server. The syntax of this method is as follows:
CreateParameter([Name], [Type], [Direction], [Size], [Value]) As Parameter |
Each argument you pass to this method is assigned to a property of the Parameter object being created. We'll go over these properties in more detail in the next section.